IF OBJECT_ID('dbo.GetSubContractSpecialists') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.GetSubContractSpecialists
    IF OBJECT_ID('dbo.GetSubContractSpecialists') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.GetSubContractSpecialists >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.GetSubContractSpecialists >>>'
END
go
-- =======================================================================
-- Procedure Name: dbo.GetSubContractSpecialists
--
-- Author:      Kevin Cashman, Parallax Consulting LLC
--
-- Create date: 10/11/2008
--
-- Description: Returns list of SubContract Specialists for Deliverables
--
-- Parameters:  None
--              
-- Returns:     INT, 0=Success, -1=Error
--
-- SampleCall:
-- EXECUTE dbo.GetSubContractSpecialists()
--
--------------------------------------------------------------------------
-- Date        Initials  Modification
--------------------------------------------------------------------------
-- 10/12/08    KC        Created
-- 12/09/08    KC        Added @Institution as a Param.
-- 12/17/08    KC        changed Security to new function
-- 01/05/08    KC        Security Function Perf Issues: use TempTable
-- 01/07/08    KC        Roll back temp table change.
--
-- =======================================================================
CREATE PROCEDURE dbo.GetSubContractSpecialists
     (@Name                        VARCHAR(50)=NULL,
     @Institution                  VARCHAR(50)=NULL,
     @DomainUserId                 INT
     )
AS
BEGIN
     /*  Returned Fields for Dropdown
     --------------------------------
     Name           VARCHAR             
     */

     -- Initial Settings
     SET NOCOUNT ON
     
     -- Declare Local Variables
     DECLARE   @Error            INT,
               @RowCount         INT
               
     IF @Institution IN ('ALL','','0') SET @Institution = NULL
      
               
     -- Result Query    
     SELECT DISTINCT D.SubmittedBy AS SubContractSpecialist
     FROM      dbo.vwInfoEdSubContractDeliverables D 
                    INNER JOIN dbo.AgrAgreement_Info AI ON D.prop_no = AI.InfoEdPropNumber
                    -- Security Issues: Not all Users can see all Agreements!
                    INNER JOIN
                         dbo.fnGetSecurityAgreements(@DomainUserId) AUTH ON AI.AgreementId = AUTH.AgreementId
     WHERE     D.SubmittedBy IS NOT NULL AND
               ((D.SubmittedBy LIKE '%' + @Name + '%') OR @Name IS NULL) AND
               (@Institution IS NULL OR AI.InstitutionId IN (SELECT Criteria_ID FROM dbo.fnGetMultiCriteria(@Institution)))
     ORDER BY  SubContractSpecialist
     
     SELECT @Error=@@ERROR,@RowCount=@@ROWCOUNT
     IF @Error!=0
          RETURN -1

     -- Return Success
     RETURN 0
     
END
go

IF OBJECT_ID('dbo.GetSubContractSpecialists') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE dbo.GetSubContractSpecialists >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.GetSubContractSpecialists >>>'
go

GRANT EXECUTE ON dbo.GetSubContractSpecialists TO InsightReport,Insight
go

